/**
 * 
 */
package com.elite.groovy.callback

import cn.com.dh.AESUtil
import com.elite.groovy.util.EliteBean
import com.elite.groovy.util.Util
import net.sf.json.JSONArray
import net.sf.json.JSONObject
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse //导入grooby的json解析api包

/**
 * 
 * @author luper
 *
 */


public  class GwZtsOrder implements ServiceBackInvoke{
  private static final Log log = LogFactory.getLog(GwZtsOrder.class);
  public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response){
		log.debug("-----Begin 官网投诉与建议工单----------");
		response.setHeader("Content-type", "text/html;charset=UTF-8");
		response.setCharacterEncoding("UTF-8");
		//String c_ContNo=request.getParameter("contNo");
		String aesReq =Util.getRequestBody(request, "UTF-8");
		log.debug("aesReq:" + aesReq);
		
		
		
		request.getParameter("interfacetype")
		//获取接口请求类型：create 官网准投诉新建/search 官网准投诉受理单查询
		String v_interfacetype=request.getParameter("interfacetype");
		log.debug("v_interfacetype:"+v_interfacetype);
		try{

			//官网准投诉新建
			if(v_interfacetype=='create'){
			
				JSONObject aesJson  = new JSONObject().fromObject(aesReq);
				String aesData=aesJson.getString("data");

				//aes密钥
				String assertKey = "+ZAb13}Pi0xQ!lVu";
				//aes解密
				log.debug("解密前数据data是："+aesData);
				String requestJSONStr=AESUtil.decrypt(aesData,assertKey);
				log.debug("解密后数据32212是："+requestJSONStr);
			
			
				JSONObject json  = new JSONObject().fromObject(requestJSONStr);
				
				String v_msgtype = "";
				if(json.has("msgtype")&&json.getString("msgtype").length()>0){//留言类型（TS/JY 投诉/建议）
					v_msgtype = json.getString("msgtype");               
				}else{
					//返回失败结果
					JSONObject  jsonALL3 = new JSONObject();
					jsonALL3.put("retCode", "-1");
					jsonALL3.put("retMessage", "留言类型不能为空");
					jsonALL3.put("data", "");
					return jsonALL3;
				}
				String v_channelsource = "";
				if(json.has("channelsource")&&json.getString("channelsource").length()>0){//数据来源
					v_channelsource = json.getString("channelsource");               
				}else{
					//返回失败结果
					JSONObject  jsonALL3 = new JSONObject();
					jsonALL3.put("retCode", "-1");
					jsonALL3.put("retMessage", "数据来源不能为空");
					jsonALL3.put("data", "");
					return jsonALL3;
				}
				String v_customername = "";
				if(json.has("customername")&&json.getString("customername").length()>0){ //客户姓名
					v_customername = json.getString("customername");
				}else{
					//返回失败结果
					JSONObject  jsonALL3 = new JSONObject();
					jsonALL3.put("retCode", "-1");
					jsonALL3.put("retMessage", "客户姓名不能为空");
					jsonALL3.put("data", "");
					return jsonALL3;
				}
				String v_mobile = "";
				if(json.has("mobile")&&json.getString("mobile").length()>0){//联系方式
					v_mobile = json.getString("mobile");       	  		
				}else{
					//返回失败结果
					JSONObject  jsonALL3 = new JSONObject();
					jsonALL3.put("retCode", "-1");
					jsonALL3.put("retMessage", "联系方式不能为空");
					jsonALL3.put("data", "");
					return jsonALL3;
				}
				String v_gender ="";
				if(json.has("gender")){//性别
					v_gender = json.getString("gender");        			
				}
				String v_email ="";
				if(json.has("email")){//邮箱
					v_email = json.getString("email");      				
				}
				String v_carno ="";
				if(json.has("carno")){//车牌号
					v_carno = json.getString("carno");      				
				}
				String v_policyno = "";
				if(json.has("policyno")){//保单号
					v_policyno = json.getString("policyno");        		
				}
				String v_province ="";
				if(json.has("province")){//所在省
					v_province = json.getString("province");      		
				}
				String v_city ="";
				if(json.has("city")){//所在市
					v_city = json.getString("city");        				
				}
				String v_content = "";
				if(json.has("content")){//内容描述
					 v_content = json.getString("content");        		
				}
				String v_title ="";
				if(json.has("title")){//标题
					 v_title = json.getString("title");					
				}
				String v_userid ="";
				if(json.has("userID")){//官微传递userID
					v_userid = json.getString("userID");					
				}
				String v_isnwemployee ="";
				if(json.has("isnwemployee")){//官微传递userID
					v_isnwemployee = json.getString("isnwemployee");					
				}
				
				def v_rule_parent="";
				def v_rule="";
				
				if(v_channelsource=='10002000'){
					v_rule_parent="官微投诉";
					v_rule="官微投诉";
				}else{
					v_rule_parent="官网投诉建议"
					if(v_msgtype=='TS'){
						v_rule="官网投诉";
					}
					if(v_msgtype=='JY'){
						v_rule="官网建议";
					}
				}

				
				//生成objective_guid  工单号
				def querysql11="select sysguid objective_guid,'GWTS'||to_char(sysdate,'YYYYMMDDhh24mi')||(case when SEQ_GWZTSORDER.nextval>=1 and SEQ_GWZTSORDER.nextval<10 then '000'||SEQ_GWZTSORDER.nextval  when SEQ_GWZTSORDER.nextval>=10 and SEQ_GWZTSORDER.nextval<100  then '00'||SEQ_GWZTSORDER.nextval  when SEQ_GWZTSORDER.nextval>=100 and SEQ_GWZTSORDER.nextval<1000  then '0'||SEQ_GWZTSORDER.nextval else to_char(SEQ_GWZTSORDER.nextval)end) workorderid,'"+v_province+"'||'"+v_city+"' address from dual";
				def list11=eliteBean.chaxun_sql(querysql11,"dataSource");
				def map11=list11[0];
				def v_objective_guid=map11.get("objective_guid");
				def v_workorderid=map11.get("workorderid");
				def v_address=map11.get("address");	
			 
				//新老客户检查
				def querysql1="select count(*) count1 from customer where customername='"+v_customername+"' and mobile='"+v_mobile+"'";
				def list1=eliteBean.chaxun_sql(querysql1,"dataSource");

				def map1=list1[0];
				def v_count1=map1.get("count1");
				def v_customer_guid = "";
				
				 //新客户新建准投诉
				if(v_count1<=0){
				 //新customer_guid
				  def querysql2="select sysguid new_customer_guid from dual";
				  def list2=eliteBean.chaxun_sql(querysql2,"dataSource");
				  def map2=list2[0];
				  v_customer_guid=map2.get("new_customer_guid");

				 //生成 customer
				  def updatesql3="insert into customer(CUSTOMER_GUID,customertype_id,CUSTOMERNAME,MOBILE,WORKSTATUS,e_mail,address)select '"+v_customer_guid+"','GWjoin','"+v_customername+"','"+v_mobile+"','ONLINE','"+v_email+"','"+v_address+"'  FROM dual";
				  def updatesqlresult3=eliteBean.update(updatesql3,"dataSource");
				}
				
				//老客户新建准投诉
				if(v_count1>0){
				  //查已有的customer_guid
				  def querysql7="select customer_guid customer_guid from customer where customername='"+v_customername+"' and mobile='"+v_mobile+"' and rownum=1";
				  def list7=eliteBean.chaxun_sql(querysql7,"dataSource");
				  def map7=list7[0];
				  v_customer_guid=map7.get("customer_guid");
				  
				 //更新 customer 的邮箱和所在地信息
				  def updatesql28="update customer set e_mail='"+v_email+"',address='"+v_address+"' where customer_guid='"+v_customer_guid+"'";
				  def updatesqlresult28=eliteBean.update(updatesql28,"dataSource");
				}
				
				//生成 objective
				def updatesql4="insert into objective(OBJECTIVE_GUID, OBJECTIVETYPE_ID,HANDLEGROUP_ID,PRIORITY_ID, EXPIREDDATE, CUSTOMER_GUID,OBJECTIVESTATUS,CREATEDBY, CREATEDDATE,WORKORDERFRAME_ID,CATEGORY,OBJECTIVESUBSTATUS,MAP_ID,CURSTEP_ID_MAP,QA_FLAG,CALLBY,CALLTEL,POLICYNO,CARNO,REOPEN,workorderid,SOURCE,ispolicy,isnwemployee,channelsource)values('"+v_objective_guid+"','ZTSGDW','ORG016','NORMAL',sysdate+5,'"+v_customer_guid+"','OPEN','000146',sysdate,'3EN351','WORDER','BZGD04','3EN351','SS2SZE','0','"+v_customername+"','"+v_mobile+"','"+v_policyno+"','"+v_carno+"','0','"+v_workorderid+"','GW','1','"+v_isnwemployee+"','"+v_channelsource+"')";
				def updatesqlresult4=eliteBean.update(updatesql4,"dataSource");
				 
				//生成 elitetask 
				def updatesql5="insert into elitetask(ELITETASK_GUID,OBJECTIVE_GUID,OBJECTIVETYPE_ID,EXPECTSTARTTIME,EXPECTENDTIME,PRIORITY,HANDLEGROUP_ID,CUSTOMER_GUID,CURSTEPSTATUS,STEPFORECASTDATE,CURSTEP_ID_MAP, MAP_ID, OBJECTIVESUBSTATUS,OBFLAG)values(sysguid,'"+v_objective_guid+"','ZTSGDW',sysdate,sysdate,'NORMAL','ORG016','"+v_customer_guid+"','OPEN',sysdate,'SS2SZE','3EN351','BZGD04','0')";
				def updatesqlresult5=eliteBean.update(updatesql5,"dataSource");
				
				//生成 tabc 
				def updatesql6="insert into tabcorder_zts(RELATION_ID,IF_VISIT,DELAY_FLAG,CANCELSTATUS,RECEPTION_TIME, WARNING_TIME, ENDTIME,CONTENT,rule_parent,rule,title,userid)values('"+v_objective_guid+"','0','-1','0',to_char(sysdate ,'yyyy-mm-dd'),to_char(sysdate+ (select EXPIREDPERIOD from WO3_FRAME_MAP where map_id='3EN351')-1,'yyyy-mm-dd'),to_char(sysdate+ (select EXPIREDPERIOD from WO3_FRAME_MAP where map_id='3EN351'),'yyyy-mm-dd'),'"+v_content+"','"+v_rule_parent+"','"+v_rule+"','"+v_title+"','"+v_userid+"')";
				def updatesqlresult6=eliteBean.update(updatesql6,"dataSource");
				
				//保存照片
				if(json.has("photos")){//官微传递图片集
					JSONArray  v_photos = json.getJSONArray("photos");	
					for(int i=0;i<v_photos.size();i++){
						String v_url = v_photos.get(i);
						def updatephoto="insert into uploadlist (UPLOADGUID, CREATEDATE, PATH ,FILENAME ,STAFF_ID, GUID,CONFIDENTIAL_TYPE ,UPLOAD_STATUS) values(sysguid,sysdate,'"+v_url+"','','000146','"+v_objective_guid+"','1','0')";
						def updatephotoresult=eliteBean.update(updatephoto,"dataSource");
					}
				}
				
				
				String aesResData=AESUtil.encrypt("{\"workorderid\":\""+v_workorderid+"\"}",assertKey);
				
				//返回成功结果
				JSONObject  jsonALL3 = new JSONObject();
				jsonALL3.put("retCode", "1");
				jsonALL3.put("retMessage", "success");
				jsonALL3.put("data", aesResData);
				return jsonALL3;
				
			}
		}catch(Exception e){
			log.debug("新建官网投诉与建议工单错误："+e);
			
			//返回失败结果
			JSONObject  jsonALL3 = new JSONObject();
			jsonALL3.put("retCode", "-1");
			jsonALL3.put("retMessage", "创建错误，请联系系统管理员");
			jsonALL3.put("data", "");
			return jsonALL3;
		}
		try{
			if(v_interfacetype=='search'){
				
				JSONObject aesJson  = new JSONObject().fromObject(aesReq);
				String aesData=aesJson.getString("data");

				//aes密钥
				String assertKey = "+ZAb13}Pi0xQ!lVu";
				//aes解密

				log.debug("解密前数据data是："+aesData);
				String requestJSONStr=AESUtil.decrypt(aesData,assertKey);
				log.debug("解密后数据32212是："+requestJSONStr);

			
				def SQL1="";
				def SQL2="";
				def SQL3="";
				def SQL4="";

				log.debug("解密后数据123是："+requestJSONStr);


				JSONObject json  = new JSONObject().fromObject(requestJSONStr);
				
				String v_mobile ="";
				if(json.has("mobile")){//联系方式
					v_mobile = json.getString("mobile");        				
				}
				String v_carno ="";
				if(json.has("carno")){//车牌号
					v_carno = json.getString("carno");        				
				}
				String v_policyno ="";
				if(json.has("policyno")){//保单号
					v_policyno = json.getString("policyno");        				
				}
				String v_userID ="";
				if(json.has("userID")){//官微userID
					v_userID = json.getString("userID");        				
				}

				log.debug("v_mobile:"+v_mobile);
				log.debug("v_carno:"+v_carno);
				log.debug("v_policyno:"+v_policyno);
				log.debug("v_userID:"+v_userID);
				
				boolean flag = false;
				
				if(v_mobile !="" && v_mobile != null && !"null".equals(v_mobile)){
					 SQL1 ="and  o.CALLTEL='"+v_mobile+"'";
					 flag= true;
				}
				if(v_carno !=""  && v_carno != null && !"null".equals(v_carno)){
					 SQL2 ="and  o.carno='"+v_carno+"'";
					 flag= true;
				}
				if(v_policyno !="" && v_policyno != null && !"null".equals(v_policyno)){
					 SQL3 ="and  o.policyno='"+v_policyno+"'";
					 flag= true;
				}
				if(v_userID !="" && v_userID != null && !"null".equals(v_userID)){
					 SQL4 ="and  z.userid='"+v_userID+"'";
					 flag= true;
				}
				log.debug("SQL1:"+SQL1);
				log.debug("SQL2:"+SQL2);
				log.debug("SQL3:"+SQL3);
				log.debug("SQL4:"+SQL4);
				if(!flag){
					JSONObject  jsonALL1 = new JSONObject();
					jsonALL1.put("retCode", "-1");
					jsonALL1.put("retMessage","查询失败，请输入查询条件");
					jsonALL1.put("data", "");
					log.debug("jsonALL1:"+jsonALL1);
					return jsonALL1; 
				}
				
				//官网受理单查询
				def querysql20="select count(*) count2 from objective o left join tabcorder_zts z on o.objective_guid = z.relation_id where o.MAP_ID='3EN351' and o.source='GW' "+SQL1+" "+SQL2+" "+SQL3+" "+SQL4+" ";
				def list20=eliteBean.chaxun_sql(querysql20,"dataSource");
				log.debug("list20-------------------------------"+list20);
				def map20=list20[0];
				log.debug("map20-------------------------------"+map20);
				def v_count2=map20.get("count2");
				log.debug("v_count2-------------------------------"+v_count2);
				
				
				
				if(v_count2>0){
					def querysq2l="SELECT o.objective_guid v_guid,z.title v_title,z.content v_content,z.userid v_userID,to_char(o.createddate,'yyyy-mm-dd hh24:mi:ss') v_createddate,ct.customername v_customername,ct.mobile v_mobile,ct.address v_address,o.carno v_carno,o.policyno v_policyno,case when o.OBJECTIVESTATUS='OPEN' then '处理中' else '已完成' end v_statudesc,'鼎和回复：'||c.COMMENTS v_reply, to_char(c.modifieddate,'yyyy-mm-dd hh24:mi:ss') v_replydate FROM objective o left join tabcorder_zts z on o.objective_guid = z.relation_id  left join customer ct on o.customer_guid = ct.customer_guid left join (select WORKORDER_GUID ,MODIFIEDDATE,COMMENTS  from (SELECT WORKORDER_GUID ,MODIFIEDDATE,COMMENTS ,ROW_NUMBER() OVER(partition by A.WORKORDER_GUID order by A.MODIFIEDDATE desc nulls last) rn FROM workorderstep3 A) B   WHERE b.RN = 1)c on c.WORKORDER_GUID=o.objective_guid  where  MAP_ID='3EN351' and o.source='GW' "+SQL1+" "+SQL2+" "+SQL3+" "+SQL4+" order by o.createddate desc";			
					List<?> l =eliteBean.chaxun_sql(querysq2l,"dataSource");
					log.debug("l:"+l);
					int count=l.size();
					if(l.size()>=1) {
						JSONObject ja = new JSONObject();
						JSONObject jb = new JSONObject();
						JSONArray jc = new JSONArray();
						ja.put("retCode", "1");
						ja.put("retMessage", "success");
						for(int i = 0;i<l.size();i++){
							jb.clear();
							Map<?, ?> m = (Map<?, ?>) l.get(i);
							log.debug("Query:"+m.toString());
							jb.put("title", m.get("v_title")  ==null?"":(String)m.get("v_title"));
							jb.put("content", m.get("v_content")  ==null?"":(String)m.get("v_content"));
							jb.put("reply", m.get("v_reply")  ==null?"":(String)m.get("v_reply"));
							
							def queryPhotos = "select path from uploadlist where guid = '"+m.get("v_guid")+"' ";
							List<?> photos =eliteBean.chaxun_sql(queryPhotos,"dataSource");
							def photostr = "";
							if(photos.size()>=1){
								JSONArray JSONarrPhotos = new JSONArray();
								for(int j=0;j<photos.size();j++){
									Map<?, ?> p = (Map<?, ?>)photos.get(j);
									JSONarrPhotos.add((String)p.get("PATH"))
								}
								photostr = JSONarrPhotos.toString();
							}
							jb.put("photos",photostr);
							
							jb.put("customername", m.get("v_customername")  ==null?"":(String)m.get("v_customername"));
							jb.put("mobile", m.get("v_mobile")  ==null?"":(String)m.get("v_mobile"));
							jb.put("carno", m.get("v_carno")  ==null?"":(String)m.get("v_carno"));
							jb.put("policyno", m.get("v_policyno")  ==null?"":(String)m.get("v_policyno"));
							jb.put("address", m.get("v_address")  ==null?"":(String)m.get("v_address"));
							jb.put("createddate", m.get("v_createddate")  ==null?"":(String)m.get("v_createddate")); 
							jb.put("replydate", m.get("v_replydate")  ==null?"":(String)m.get("v_replydate"));
							jb.put("statudesc", m.get("v_statudesc")  ==null?"":(String)m.get("v_statudesc"));
							jc.add(jb);
						}
						String jcStr = jc.toString();
						log.debug("加密前:"+jcStr);
						String aesResData=AESUtil.encrypt(jcStr,assertKey);
						log.debug("加密后:"+aesResData);
						ja.put("data", aesResData);
						return ja;
					}
				}else{
					JSONObject  jsonALL1 = new JSONObject();
					jsonALL1.put("retCode", "1");
					jsonALL1.put("retMessage","查询成功，未查询相关内容");
					jsonALL1.put("data", "");
					log.debug("jsonALL1:"+jsonALL1);
					return jsonALL1; 
				
				}
			}
		}catch(Exception e){
			log.debug("查询官网投诉与建议工单错误："+e);
			
			//返回失败结果
			JSONObject  jsonALL3 = new JSONObject();
			jsonALL3.put("retCode", "-1");
			jsonALL3.put("retMessage", "查询失败，请联系系统管理员");
			jsonALL3.put("data", "");
			return jsonALL3;
		}
		log.debug("-----End 官网投诉与建议工单----------");
      }  
        
 }
